package com.palbrattberg.excel2sql;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.text.SimpleDateFormat;
import java.util.TimeZone;

import jxl.Cell;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.biff.DateRecord;
import jxl.write.biff.NumberRecord;

/**
 * Convert Excel to SQL.
 */
public class Excel2SQL {

    public static void main(String[] args) {
        if (args.length == 1) {
            System.out.println(Excel2SQL.generateInsert(args[0]));
        } else if (args.length == 2) {
            try {
                String output = Excel2SQL.generateInsert(args[0]);
                FileWriter fstream = new FileWriter(args[1]);
                BufferedWriter out = new BufferedWriter(fstream);
                out.write(output);
                out.close();
            } catch (Exception e) {
                System.err.printf("Error while parsing file '%s' and saving to file '%s': %s", (Object) args, e
                    .getMessage());
            }
        } else {
            System.err.println("Error! You must supply a path to a "
                    + "valid Excel-file as the first argument to this program.\n"
                    + "You may additionally supply a second file name that the SQL will be output to.");
        }
    }

    /**
     * Generate SQL INSERT string for populating a database with all the data defined in the Excel file.
     * 
     * @param excelFile Location of Excel-file
     * @return A {@link String} with SQL commands.
     */
    public static String generateInsert(final String excelFile) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        dateFormat.setTimeZone(TimeZone.getTimeZone("GMT + 0")); // let excel decide the time

        File infile = new File(excelFile);

        StringBuilder sb = new StringBuilder();
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(infile);

            for (Sheet sheet : workbook.getSheets()) {
                if (sheet == null || sheet.getRows() < 3 || sheet.getColumns() < 1) {
                    break; // not enough data
                }

                StringBuilder header = new StringBuilder();
                header.append(String.format("INSERT INTO %s (", sheet.getCell(0, 0).getContents()));

                boolean first = true;
                for (Cell cell : sheet.getRow(1)) {
                    if (!first)
                        header.append(", ");
                    else
                        first = false;
                    header.append(cell.getContents());
                }
                if (first) {
                    break; // no columns
                }
                header.append(") VALUES (");

                for (int i = 2; i < sheet.getRows(); i++) {
                    sb.append(header);
                    first = true;
                    for (Cell cell : sheet.getRow(i)) {
                        if (!first)
                            sb.append(", ");
                        else
                            first = false;
                        if (cell instanceof DateCell || cell instanceof DateRecord) {
                            DateCell dateCell = (DateCell) cell;
                            sb.append("'");
                            sb.append(dateFormat.format(dateCell.getDate()));
                            sb.append("'");
                        } else if (cell instanceof NumberCell || cell instanceof NumberRecord) {
                            sb.append(cell.getContents());
                        } else if (cell.getCellFormat() != null
                                && cell.getCellFormat().getBackgroundColour().getValue() != Colour.DEFAULT_BACKGROUND
                                    .getValue()) {
                            // other background than default? no quotes baby.
                            sb.append(cell.getContents());
                        } else {
                            // normal case, quote value
                            sb.append("'");
                            sb.append(cell.getContents());
                            sb.append("'");
                        }
                    }
                    sb.append(");\n");
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(String.format("Error when parsing Excel file '%s'", excelFile), e);
        } finally {
            if (workbook != null)
                workbook.close();
        }
        return sb.toString();
    }
}
